from django.shortcuts import render,HttpResponse

from stu_exam_manage.models import Lilun_title, Lilun_unit_name

import  xlrd
import os
import json


#  自定义理论题目文件夹路径
course_id = 1   # ** 必填 ** 请查询Course表查看课程对应的课程id
path_lilun_xls_path = r'D:\11机器学习2\机器学习2试题\机器学习2'


# Create your views here.
# 将所有的理论题写入数据库
def writeToDB(xlspath):
    '''
    将所有的理论题写入数据库
    :param xlspath: 所有xls理论题的xls路径
    :return: None
    '''

    basename = os.path.basename(xlspath)    #获取文件名
    workbook = xlrd.open_workbook(xlspath)  #打开文件
    print(workbook.sheet_names())

    sheet1 = workbook.sheet_by_index(0) #获取工作表
    print(sheet1.name,sheet1.nrows,sheet1.ncols)

    kaoti = Lilun_unit_name(name=basename, course_id=course_id)
    kaoti.save()
    for i in range(1,sheet1.nrows):
        # Exam(*sheet1.row_values(i),kaoti=kaoti).save()
        try:
            Lilun_title(
                bianhao = i,
                kaoti = kaoti,
                zhishidian = sheet1.row_values(i)[0],
                leixing    = sheet1.row_values(i)[1],
                tigan      = sheet1.row_values(i)[2],
                xxa        = sheet1.row_values(i)[3],
                xxb        = sheet1.row_values(i)[4],
                xxc        = sheet1.row_values(i)[5],
                xxd        = sheet1.row_values(i)[6],
                xxe        = sheet1.row_values(i)[7],
                xxf        = sheet1.row_values(i)[8],
                daan       = sheet1.row_values(i)[9],
                jiexi      = sheet1.row_values(i)[10],
                chutiren   = sheet1.row_values(i)[11],
            ).save()
        except Exception as e:
            print('报错信息：',e)

    print('read success')


# 获取所有存放xls文件的路径
def query(path):
    '''
    # 获取所有存放xls文件的路径
    :param path:  存放xls文件的文件夹路径
    :return:    返回所有的xls文件的路径
    '''

    all_xls_paths = []
    for i in os.walk(path):
        # print(i)
        if len(i[1])==0:
            for j in i[2]:
                # print(j)
                # if j.strip().endswith('.xls') or j.strip().endswith('.xlsx'):
                if j.strip().endswith('理论.xls') or j.strip().endswith('理论.xlsx'):
                    all_xls_paths.append(os.path.join(i[0],j))
    # print(len(set(all_xls_paths)),set(all_xls_paths))
    for i in all_xls_paths:
        print(i)    # 110
        writeToDB(i)


# 保存理论题目的入口
def save_new_xls_liluntitles(request):
    '''
    保存理论题目的入口
    :param request: django服务器调用url请求
    :return:    None
    '''

    #  自定义理论题目文件夹路径
    path = path_lilun_xls_path
    query(path)     # 进入保存模式

    return HttpResponse('保存理论题目成功')


# 清空所有的理论题表以及理论题目表
def delete_all_xls_liluntitles(request):
    '''
    清空所有的理论题表以及理论题目表
    :param request:
    :return:
    '''
    try:
        Lilun_title.objects.all().delete()
        Lilun_unit_name.objects.all().delete()
    except Exception as e:
        return HttpResponse('清空理论题目表和理论试卷表---》失败')

    return HttpResponse('已经为您清空理论题目表和理论试卷表--》成功')